/*
 * DB.java
 *
 * Created on July 30, 2007, 5:45 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package org.atomojo.auth.service.db;

import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.infoset.xml.DocumentLoader;
import org.infoset.xml.XMLException;
import org.infoset.xml.sax.SAXDocumentLoader;
import org.milowski.db.DB;
import org.milowski.db.DBCache;
import org.milowski.db.DBConnection;
import org.milowski.db.DBIterator;
import org.milowski.db.DBQueryHandler;
import org.milowski.db.DBResultConstructor;
import org.milowski.db.DBUpdateHandler;
import org.milowski.db.Slot;

/**
 *
 * @author alex
 */
public class AuthDB extends DB
{
   
   static Logger LOG = Logger.getLogger(AuthDB.class.getName());
   
   static public UUID ROOT_ROLE = UUID.fromString("1434b5c6-5e52-4096-9312-f1060b1a38b5");
   static public UUID QUERY_ROLE = UUID.fromString("696a9356-2a27-4347-8bcf-2db548e4cd67");
   static public UUID REALM_ROOT_ROLE = UUID.fromString("497d7f39-f9d2-468b-91c8-34ceca927f78");
   static public UUID EDITOR_ROLE = UUID.fromString("f046e2e3-4513-40b5-9db9-69ce057fa1f9");
   static public UUID SUPERUSER_PERMISSION = UUID.fromString("fce0c8c9-35a2-4720-bf44-daf5790cdaf0");
   static public UUID REALM_SUPERUSER_PERMISSION = UUID.fromString("6a7986f8-a5e9-4afb-b221-c8d65a92927b");
   static public UUID ACROSS_REALM_PERMISSION = UUID.fromString("f5176c38-af47-4788-9dfc-1cccc25291cf");
   static public UUID QUERY_PERMISSION = UUID.fromString("b5289ab7-b767-43a7-9039-b6b5735a2ac9");
   
   /*
   static String getProperty(String [] spec) {
      String value = System.getProperty(spec[0]);
      if (value==null) {
         value = spec[1];
      }
      return value;
   }
   public static AuthDB createDB(File dir,String name)
   {
      AuthDB db = new AuthDB(new File(dir,name));
      return db;
   }
    */
   
   static int EXPIRATION = 5*60*100;
   static {
      String value = System.getProperty("atomojo.db.cache.expiration");
      if (value!=null) {
         EXPIRATION = Integer.parseInt(value);
      }
   }
   
   class RealmUsers {
      Map<Realm,DBCache<UUID,RealmUser>> realms = new TreeMap<Realm,DBCache<UUID,RealmUser>>();
      
      DBCache<UUID,RealmUser> get(Realm realm) {
         DBCache<UUID,RealmUser> cache = realms.get(realm);
         if (cache==null) {
            synchronized (realms) {
               cache = new RealmUserCache(realm);
               realms.put(realm,cache);
            }
         }
         return cache;
      }
      
      void remove(Realm realm) {
         synchronized (realms) {
            realms.remove(realm);
         }
      }
   }
   class RealmUserCache extends DBCache<UUID,RealmUser> {
      Realm realm;
      RealmUserCache(Realm realm) {
         super(100,EXPIRATION,true);
         this.realm = realm;
      }
      
      protected RealmUser fetch(final UUID id) 
         throws SQLException
      {
         final User user = userCache.get(id);
         if (user!=null) {
            final Slot<RealmUser> u = new Slot<RealmUser>();
            final DBConnection connection = getConnection();
            try {
               connection.query(REALM_USER_BY_USER, new DBQueryHandler() {
                  public void prepare(PreparedStatement s)
                     throws SQLException
                  {
                     s.setInt(1,realm.getId());
                     s.setInt(2,user.getId());
                  }
                  public void onResults(ResultSet set) 
                     throws SQLException
                  {
                     if (set.next()) {
                        final int rid = set.getInt(1);
                        final Slot<String> alias = new Slot<String>();
                        connection.query(REALM_USER_ALIAS, new DBQueryHandler() {
                           public void prepare(PreparedStatement s)
                              throws SQLException
                           {
                              s.setInt(1,rid);
                           }
                           public void onResults(ResultSet set) 
                              throws SQLException
                           {
                              if (set.next()) {
                                 alias.set(set.getString(1));
                              }
                           }
                        });
                        u.set(new RealmUser(AuthDB.this,rid,realm,user,alias.get(),set.getString(2),set.getString(3)));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return u.get();
         } else {
            return null;
         }
      }
      protected RealmUser fetchById(final Integer id) 
         throws SQLException
      {
         final Slot<RealmUser> u = new Slot<RealmUser>();
         final DBConnection connection = getConnection();
         try {
            connection.query(REALM_USER, new DBQueryHandler() {
               public void prepare(PreparedStatement s)
                  throws SQLException
               {
                  s.setInt(1,id.intValue());
               }
               public void onResults(ResultSet set) 
                  throws SQLException
               {
                  if (set.next()) {
                     final Slot<String> alias = new Slot<String>();
                     connection.query(REALM_USER_ALIAS, new DBQueryHandler() {
                        public void prepare(PreparedStatement s)
                           throws SQLException
                        {
                           s.setInt(1,id);
                        }
                        public void onResults(ResultSet set) 
                           throws SQLException
                        {
                           if (set.next()) {
                              alias.set(set.getString(1));
                           }
                        }
                     });
                     User user = userCache.get(set.getInt(1));
                     u.set(new RealmUser(AuthDB.this,id,realm,user,alias.get(),set.getString(2),set.getString(3)));
                  }
               }
            });
         } finally {
            release(connection);
         }
         return u.get();
     }
      protected RealmUser fetchByName(final String name) 
         throws SQLException
      {
         final Slot<RealmUser> u = new Slot<RealmUser>();

         final DBConnection connection = getConnection();
         try {
            connection.query(REALM_USER_BY_ALIAS, new DBQueryHandler() {
               public void prepare(PreparedStatement s)
                  throws SQLException
               {
                  s.setInt(1,realm.getId());
                  s.setString(2,name);
               }
               public void onResults(ResultSet set) 
                  throws SQLException
               {
                  if (set.next()) {
                     User user = userCache.get(set.getInt(2));
                     u.set(new RealmUser(AuthDB.this,set.getInt(1),realm,user,name,set.getString(3),set.getString(4)));
                  }
               }
            });
         } finally {
            release(connection);
         }
         
         if (u.get()==null) {
            final User user = userCache.getNamed(name);
            if (user!=null) {
               final DBConnection connection2 = getConnection();
               try {
                  connection2.query(REALM_USER_BY_INHERITED_ALIAS, new DBQueryHandler() {
                     public void prepare(PreparedStatement s)
                        throws SQLException
                     {
                        s.setInt(1,realm.getId());
                        s.setInt(2,user.getId());
                     }
                     public void onResults(ResultSet set) 
                        throws SQLException
                     {
                        if (set.next()) {
                           u.set(new RealmUser(AuthDB.this,set.getInt(1),realm,user,null,set.getString(2),set.getString(3)));
                        }
                     }
                  });
               } finally {
                  release(connection2);
               }
               
            }
         }
         return u.get();
      }

      protected int getId(RealmUser r) {
         return r.getId();
      }

      protected UUID getFacet(RealmUser r) {
         return r.getUser().getUUID();
      }

      protected String getName(RealmUser r) {
         return r.getAlias();
      }
   };
   
   class RealmGroups {
      Map<Realm,DBCache<UUID,Group>> realms = new TreeMap<Realm,DBCache<UUID,Group>>();
      
      DBCache<UUID,Group> get(Realm realm) {
         DBCache<UUID,Group> cache = realms.get(realm);
         if (cache==null) {
            synchronized (realms) {
               cache = new GroupCache(realm);
               realms.put(realm,cache);
            }
         }
         return cache;
      }
      
      void remove(Realm realm) {
         synchronized (realms) {
            realms.remove(realm);
         }
      }
      
      void clear() {
         synchronized (realms) {
            realms.clear();
         }
      }
   }

   class GroupCache extends DBCache<UUID,Group> {
      Realm realm;
      GroupCache(Realm realm) {
         super(100,EXPIRATION,true);
         this.realm = realm;
      }
      protected Group fetch(final UUID id) 
         throws SQLException
      {
         final Slot<Group> g = new Slot<Group>();
         DBConnection connection = getConnection();
         try {
            connection.query(GROUP_BY_UUID, new DBQueryHandler() {
               public void prepare(PreparedStatement s) 
                  throws SQLException
               {
                  s.setInt(1,realm.getId());
                  s.setString(2,id.toString());
               }
               public void onResults(ResultSet set)
                  throws SQLException
               {
                  if (set.next()) {
                     g.set(new Group(AuthDB.this,set.getInt(1),realm,id,set.getString(2)));
                  }
               }
            });
         } finally {
            release(connection);
         }
         
         return g.get();
      }
      protected Group fetchById(final Integer id) 
         throws SQLException
      {
         final Slot<Group> g = new Slot<Group>();
         DBConnection connection = getConnection();
         try {
            connection.query(GROUP_BY_ID, new DBQueryHandler() {
               public void prepare(PreparedStatement s) 
                  throws SQLException
               {
                  s.setInt(1,realm.getId());
                  s.setInt(2,id);
               }
               public void onResults(ResultSet set)
                  throws SQLException
               {
                  if (set.next()) {
                     g.set(new Group(AuthDB.this,set.getInt(1),realm,UUID.fromString(set.getString(3)),set.getString(2)));
                  }
               }
            });
         } finally {
            release(connection);
         }
         return g.get();
     }
      protected Group fetchByName(final String name) 
         throws SQLException
      {
         final Slot<Group> g = new Slot<Group>();
         DBConnection connection = getConnection();
         try {
            connection.query(GROUP_BY_ALIAS, new DBQueryHandler() {
               public void prepare(PreparedStatement s) 
                  throws SQLException
               {
                  s.setInt(1,realm.getId());
                  s.setString(2,name);
               }
               public void onResults(ResultSet set)
                  throws SQLException
               {
                  if (set.next()) {
                     g.set(new Group(AuthDB.this,set.getInt(1),realm,UUID.fromString(set.getString(3)),set.getString(2)));
                  }
               }
            });
         } finally {
            release(connection);
         }
         return g.get();
      }

      protected int getId(Group g) {
         return g.getId();
      }

      protected UUID getFacet(Group g) {
         return g.getUUID();
      }

      protected String getName(Group g) {
         return g.getAlias();
      }
   };
   
   
   DBCache<UUID,Permission> permissionCache;
   DBCache<UUID,Role> roleCache;
   DBCache<UUID,Realm> realmCache;
   DBCache<UUID,User> userCache;
   RealmGroups realmGroupCaches;
   RealmUsers realmUserCaches;
   
   /** Creates a new instance of DB */
   public AuthDB(String name,String location)
   {
      super(name,location);
      getSchemaMap().put("auth", this.getClass().getResource("authdb.sql"));
      permissionCache = new DBCache<UUID,Permission>(-1,EXPIRATION,true) {
         protected Permission fetch(final UUID id) 
            throws SQLException
         {
            final Slot<Permission> p = new Slot<Permission>();
            DBConnection connection = getConnection();
            try {
               connection.query(PERMISSION_BY_UUID, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setString(1,id.toString());
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        p.set(new Permission(AuthDB.this,set.getInt(1),set.getString(2),id));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return p.get();
         }
         protected Permission fetchById(final Integer id) 
            throws SQLException
         {
            final Slot<Permission> p = new Slot<Permission>();
            DBConnection connection = getConnection();
            try {
               connection.query(PERMISSION_BY_ID, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setInt(1,id);
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        p.set(new Permission(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return p.get();
         }
         protected Permission fetchByName(final String name) 
            throws SQLException
         {
            final Slot<Permission> p = new Slot<Permission>();
            DBConnection connection = getConnection();
            try {
               connection.query(PERMISSION_BY_NAME, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setString(1,name);
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        p.set(new Permission(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return p.get();
         }
         
         protected int getId(Permission p) {
            return p.getId();
         }
         
         protected UUID getFacet(Permission p) {
            return p.getUUID();
         }
         protected String getName(Permission r) {
            return r.getName();
         }
      };
      roleCache = new DBCache<UUID,Role>(-1,EXPIRATION,true) {
         protected Role fetch(final UUID id) 
            throws SQLException
         {
            final Slot<Role> r = new Slot<Role>();
            DBConnection connection = getConnection();
            try {
               connection.query(ROLE_BY_UUID, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setString(1,id.toString());
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        r.set(new Role(AuthDB.this,set.getInt(1),set.getString(2),id));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return r.get();
         }
         protected Role fetchById(final Integer id) 
            throws SQLException
         {
            final Slot<Role> r = new Slot<Role>();
            DBConnection connection = getConnection();
            try {
               connection.query(ROLE_BY_ID, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setInt(1,id);
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        r.set(new Role(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return r.get();
        }
         protected Role fetchByName(final String name) 
            throws SQLException
         {
            final Slot<Role> r = new Slot<Role>();
            DBConnection connection = getConnection();
            try {
               connection.query(ROLE_BY_NAME, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setString(1,name);
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        r.set(new Role(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return r.get();
         }
         
         protected int getId(Role r) {
            return r.getId();
         }
         
         protected UUID getFacet(Role r) {
            return r.getUUID();
         }
         protected String getName(Role r) {
            return r.getName();
         }
      };
      realmCache = new DBCache<UUID,Realm>(-1,EXPIRATION,true) {
         protected Realm fetch(final UUID id) 
            throws SQLException
         {
            final Slot<Realm> r = new Slot<Realm>();
            DBConnection connection = getConnection();
            try {
               connection.query(REALM_BY_UUID, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setString(1,id.toString());
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        r.set(new Realm(AuthDB.this,set.getInt(1),set.getString(2),id));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return r.get();
         }
         protected Realm fetchById(final Integer id) 
            throws SQLException
         {
            final Slot<Realm> r = new Slot<Realm>();
            DBConnection connection = getConnection();
            try {
               connection.query(REALM_BY_ID, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setInt(1,id);
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        r.set(new Realm(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return r.get();
        }
         protected Realm fetchByName(final String name) 
            throws SQLException
         {
            final Slot<Realm> r = new Slot<Realm>();
            DBConnection connection = getConnection();
            try {
               connection.query(REALM_BY_NAME, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setString(1,name);
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        r.set(new Realm(AuthDB.this,set.getInt(1),set.getString(2),UUID.fromString(set.getString(3))));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return r.get();
         }
         
         protected int getId(Realm r) {
            return r.getId();
         }
         
         protected UUID getFacet(Realm r) {
            return r.getUUID();
         }
         
         protected String getName(Realm r) {
            return r.getName();
         }
      };
      
      userCache = new DBCache<UUID,User>(100,EXPIRATION,true) {
         protected User fetch(final UUID id) 
            throws SQLException
         {
            final Slot<User> u = new Slot<User>();
            DBConnection connection = getConnection();
            try {
               connection.query(USER_BY_UUID, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setString(1,id.toString());
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        int uid = set.getInt(1);
                        String alias = getUserAlias(uid);
                        u.set(new User(AuthDB.this,uid,id,alias,set.getString(2),set.getString(3)));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return u.get();
         }
         protected User fetchById(final Integer id) 
            throws SQLException
         {
            final Slot<User> u = new Slot<User>();
            DBConnection connection = getConnection();
            try {
               connection.query(USER_BY_ID, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setInt(1,id.intValue());
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        int uid = set.getInt(1);
                        String alias = getUserAlias(uid);
                        u.set(new User(AuthDB.this,uid,UUID.fromString(set.getString(4)),alias,set.getString(2),set.getString(3)));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return u.get();
        }
         protected User fetchByName(final String name) 
            throws SQLException
         {
            final Slot<User> u = new Slot<User>();
            DBConnection connection = getConnection();
            try {
               connection.query(USER_BY_ALIAS, new DBQueryHandler() {
                  public void prepare(PreparedStatement s) 
                     throws SQLException
                  {
                     s.setString(1,name);
                  }
                  public void onResults(ResultSet set)
                     throws SQLException
                  {
                     if (set.next()) {
                        int uid = set.getInt(1);
                        String alias = getUserAlias(uid);
                        u.set(new User(AuthDB.this,uid,UUID.fromString(set.getString(4)),alias,set.getString(2),set.getString(3)));
                     }
                  }
               });
            } finally {
               release(connection);
            }
            return u.get();
         }
         
         protected int getId(User r) {
            return r.getId();
         }
         
         protected UUID getFacet(User r) {
            return r.getUUID();
         }
         
         protected String getName(User r) {
            return r.getAlias();
         }
      };
      realmGroupCaches = new RealmGroups();
      realmUserCaches = new RealmUsers();
   }
   
   public Permission createPermission(final String name,final UUID uuid)
      throws SQLException
   {
      
      DBConnection connection = getConnection();
      try {
         int id = connection.create(CREATE_PERMISSION,LAST_ID_FROM_PERMISSIONS, new DBUpdateHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setString(1,name);
               s.setString(2,uuid.toString());
            }
         });
         return id<0 ? null : permissionCache.get(id);
      } finally {
         release(connection);
      }
   }
   
   public Permission getPermission(String name)
      throws SQLException
   {
      return permissionCache.getNamed(name);
   }
   
   public Permission getPermission(UUID uuid)
      throws SQLException
   {
      return permissionCache.get(uuid);
   }
   
   public Iterator<Permission> getPermissions() 
      throws SQLException
   {
      final Slot<Iterator<Permission>> result = new Slot<Iterator<Permission>>();
      final DBConnection connection = getConnection();
      try {
         connection.query(PERMISSIONS, new DBQueryHandler() {
            public boolean shouldClose() { return false; }
            public void onResults(ResultSet set)
               throws SQLException
            {
               result.set(new DBIterator<Permission>(set,new DBResultConstructor<Permission>() {
                  public Permission newInstance(ResultSet set) 
                     throws SQLException
                  {
                     return permissionCache.get(set.getInt(1));
                  }
               },AuthDB.this,connection));
            }
         });
      } finally {
         release(connection);
      }
      return result.get();
   }
   
   public Role createRole(final String name,final UUID uuid)
      throws SQLException
   {
      DBConnection connection = getConnection();
      try {
         int id = connection.create(CREATE_ROLE,LAST_ID_FROM_ROLES, new DBUpdateHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setString(1,name);
               s.setString(2,uuid.toString());
            }
         });
         return id<0 ? null : roleCache.get(id);
      } finally {
         release(connection);
      }
   }

   public Role getRole(String name)
      throws SQLException
   {
      return roleCache.getNamed(name);
   }
   
   public Role getRole(UUID uuid)
      throws SQLException
   {
      return roleCache.get(uuid);
   }
   
   public Iterator<Role> getRoles() 
      throws SQLException
   {
      final Slot<Iterator<Role>> result = new Slot<Iterator<Role>>();
      final DBConnection connection = getConnection();
      try {
         connection.query(ROLES, new DBQueryHandler() {
            public boolean shouldClose() { return false; }
            public void onResults(ResultSet set)
               throws SQLException
            {
               result.set(new DBIterator<Role>(set,new DBResultConstructor<Role>() {
                  public Role newInstance(ResultSet set) 
                     throws SQLException
                  {
                     return roleCache.get(set.getInt(1));
                  }
               },AuthDB.this,connection));
            }
         });
      } finally {
         release(connection);
      }
      return result.get();
   }
   
   public Realm createRealm(final String name,final UUID uuid)
      throws SQLException
   {
      DBConnection connection = getConnection();
      try {
         int id = connection.create(CREATE_REALM,LAST_ID_FROM_REALMS, new DBUpdateHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setString(1,name);
               s.setString(2,uuid.toString());
            }
         });
         return id<0 ? null : realmCache.get(id);
      } finally {
         release(connection);
      }
   }
   
   public Realm getRealm(String name)
      throws SQLException
   {
      return realmCache.getNamed(name);
   }
   
   public Realm getRealm(UUID uuid)
      throws SQLException
   {
      return realmCache.get(uuid);
   }
   
   public Iterator<Realm> getRealms() 
      throws SQLException
   {
      final Slot<Iterator<Realm>> result = new Slot<Iterator<Realm>>();
      final DBConnection connection = getConnection();
      try {
         connection.query(REALMS, new DBQueryHandler() {
            public boolean shouldClose() { return false; }
            public void onResults(ResultSet set)
               throws SQLException
            {
               result.set(new DBIterator<Realm>(set,new DBResultConstructor<Realm>() {
                  public Realm newInstance(ResultSet set) 
                     throws SQLException
                  {
                     return realmCache.get(set.getInt(1));
                  }
               },AuthDB.this,connection));
            }
         });
      } finally {
         release(connection);
      }
      return result.get();
   }
   
   public boolean isUserAliasAvailable(final String alias) 
      throws SQLException
   {
      final Slot<Boolean> found = new Slot<Boolean>(false);
      if (alias!=null) {
         DBConnection connection = getConnection();
         try {
            connection.query(USER_ALIAS_EXISTS, new DBQueryHandler() {
               public void prepare(PreparedStatement s) 
                  throws SQLException
               {
                  s.setString(1,alias);
               }
               public void onResults(ResultSet set)
                  throws SQLException
               {
                  found.set(set.next());
               }
            });
         } finally {
            release(connection);
         }
      }
      return !found.get();
   }
   
   public User createUser(final UUID uuid,final String alias,final String name,final String email)
      throws SQLException
   {
      if (!isUserAliasAvailable(alias)) {
         LOG.info("Alias "+alias+" is not available");
         return null;
      }
      final Timestamp tstamp = new Timestamp((new Date()).getTime());
      DBConnection connection = getConnection();
      try {
         int id = connection.create(CREATE_USER,LAST_ID_FROM_USERS, new DBUpdateHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setString(1,uuid.toString());
               if (name==null) {
                  s.setNull(2,Types.VARCHAR);
               } else {
                  s.setString(2,name);
               }
               if (email==null) {
                  s.setNull(3,Types.VARCHAR);
               } else {
                  s.setString(3,email);
               }
               s.setTimestamp(4,tstamp);
               s.setBoolean(5,true);
            }
         });
         User user = userCache.get(id);
         try {
            if (alias!=null) {
               if (!user.changeAlias(alias)) {
                  LOG.info("Cannot change to "+alias);
                  user.delete();
                  return null;
               }
            }
         } catch (SQLException ex) {
            LOG.log(Level.SEVERE,"Cannot reserve alias for user "+uuid.toString(),ex);
            user.delete();
            return null;
         }
         return user;
      } finally {
         release(connection);
      }
   }
   
   public User getUser(String name)
      throws SQLException
   {
      return userCache.getNamed(name);
   }
   
   public User getUser(UUID uuid)
      throws SQLException
   {
      return userCache.get(uuid);
   }
   
   public User findUserByEmail(final String email)
      throws SQLException
   {
      final Slot<User> user = new Slot<User>();
      final DBConnection connection = getConnection();
      try {
         connection.query(USER_BY_EMAIL, new DBQueryHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setString(1, email);
            }
            public void onResults(ResultSet set)
               throws SQLException
            {
               if (set.next()) {
                  user.set(userCache.get(set.getInt(1)));
               }
            }
         });
      } finally {
         release(connection);
      }
      return user.get();
   }
   
   public Iterator<User> getUsers()
      throws SQLException
   {
      return getUsers(false);
   }
   
   public Iterator<User> getUsers(boolean all) 
      throws SQLException
   {
      final Slot<Iterator<User>> result = new Slot<Iterator<User>>();
      final DBConnection connection = getConnection();
      try {
         connection.query(all ? USERS : GLOBAL_USERS, new DBQueryHandler() {
            public boolean shouldClose() { return false; }
            public void onResults(ResultSet set)
               throws SQLException
            {
               result.set(new DBIterator<User>(set,new DBResultConstructor<User>() {
                  public User newInstance(ResultSet set) 
                     throws SQLException
                  {
                     return userCache.get(set.getInt(1));
                  }
               },AuthDB.this,connection));
            }
         });
      } finally {
         release(connection);
      }
      return result.get();
   }
   
   public Group createGroup(final Realm realm,final UUID uuid,final String alias)
      throws SQLException
   {
      DBCache<UUID,Group> cache = realmGroupCaches.get(realm);
      if (cache.getNamed(alias)!=null) {
         return null;
      }
      DBConnection connection = getConnection();
      try {
         int id = connection.create(CREATE_GROUP,LAST_ID_FROM_GROUPS, new DBUpdateHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setInt(1,realm.getId());
               s.setString(2,uuid.toString());
               s.setString(3,alias);
               s.setString(4,realm.getId()+"-"+alias);
            }
         });
         return cache.get(id);
      } finally {
         release(connection);
      }
   }
   
   public Group getGroup(Realm realm,String name)
      throws SQLException
   {
      return realmGroupCaches.get(realm).getNamed(name);
   }
   
   public Group getGroup(Realm realm,UUID uuid)
      throws SQLException
   {
      return realmGroupCaches.get(realm).get(uuid);
   }
   
   public Iterator<Group> getGroups(final Realm realm) 
      throws SQLException
   {
      final Slot<Iterator<Group>> result = new Slot<Iterator<Group>>();
      final DBCache<UUID,Group> cache = realmGroupCaches.get(realm);
      final DBConnection connection = getConnection();
      try {
         connection.query(GROUPS, new DBQueryHandler() {
            public boolean shouldClose() { return false; }
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setInt(1,realm.getId());
            }
            public void onResults(ResultSet set)
               throws SQLException
            {
               result.set(new DBIterator<Group>(set,new DBResultConstructor<Group>() {
                  public Group newInstance(ResultSet set) 
                     throws SQLException
                  {
                     return cache.get(set.getInt(1));
                  }
               },AuthDB.this,connection));
            }
         });
      } finally {
         release(connection);
      }
      return result.get();
   }
   
   public boolean isRealmUserAliasAvailable(final Realm realm,final User user,final String alias)
      throws SQLException
   {
      // Check for realm conflicts with specified alias
      final Slot<Boolean> found = new Slot<Boolean>(false);
      if (alias!=null) {
         // check realm against alias
         DBConnection connection = getConnection();
         try {
            connection.query(REALM_USER_BY_ALIAS, new DBQueryHandler() {
               public void prepare(PreparedStatement s) 
                  throws SQLException
               {
                  s.setInt(1,realm.getId());
                  s.setString(2,alias);
               }
               public void onResults(ResultSet set)
                  throws SQLException
               {
                  found.set(set.next());
               }
            });
            if (found.get()) {
               return false;
            }
            // check inherited aliases
            connection.query(REALM_USER_HAS_INHERITED_ALIAS, new DBQueryHandler() {
               public void prepare(PreparedStatement s) 
                  throws SQLException
               {
                  s.setInt(1,realm.getId());
                  s.setString(2,alias);
               }
               public void onResults(ResultSet set)
                  throws SQLException
               {
                  found.set(set.next());
               }
            });
            if (found.get()) {
               return false;
            }
         } finally {
            release(connection);
         }
      }
      //
      if (alias==null) {
         // Check inherited alias against local aliases
         DBConnection connection = getConnection();
         try {
            connection.query(REALM_USER_BY_ALIAS, new DBQueryHandler() {
               public void prepare(PreparedStatement s) 
                  throws SQLException
               {
                  s.setInt(1,realm.getId());
                  s.setString(2,user.getAlias());
               }
               public void onResults(ResultSet set)
                  throws SQLException
               {
                  found.set(set.next());
               }
            });
            if (found.get()) {
               return false;
            }
         } finally {
            release(connection);
         }
      }
      return true;
   }
   
   public RealmUser createRealmUser(final Realm realm,final User user,final String alias,final String name,final String email)
      throws SQLException
   {
      if (!isRealmUserAliasAvailable(realm,user,alias)) {
         return null;
      }
      final Timestamp tstamp = new Timestamp((new Date()).getTime());
      DBConnection connection = getConnection();
      try {
         int id = connection.create(CREATE_REALM_USER,LAST_ID_FROM_REALM_USERS, new DBUpdateHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setInt(1,realm.getId());
               s.setInt(2,user.getId());
               if (name==null) {
                  s.setNull(3,Types.VARCHAR);
               } else {
                  s.setString(3,name);
               }
               if (email==null) {
                  s.setNull(4,Types.VARCHAR);
               } else {
                  s.setString(4,email);
               }
               s.setTimestamp(5,tstamp);
               s.setBoolean(6,true);
            }
         });
         RealmUser ruser = realmUserCaches.get(realm).get(id);
         if (alias!=null) {
            try {
               if (!ruser.changeAlias(alias)) {
                  ruser.delete();
                  return null;
               }
            } catch (SQLException ex) {
               LOG.log(Level.SEVERE,"Cannot reserve alias for realm user "+user.getUUID().toString(),ex);
               ruser.delete();
               return null;
            }
         }
         return ruser;
      } finally {
         release(connection);
      }
   }
   
   public RealmUser getRealmUser(Realm realm,String name)
      throws SQLException
   {
      ///argh need to map by realm
      return realmUserCaches.get(realm).getNamed(name);
   }
   
   public RealmUser getRealmUser(Realm realm,UUID uuid)
      throws SQLException
   {
      return realmUserCaches.get(realm).get(uuid);
   }
   
   public RealmUser findRealmUserByEmail(final Realm realm,final String email)
      throws SQLException
   {
      final Slot<RealmUser> ruser = new Slot<RealmUser>();
      final DBConnection connection = getConnection();
      try {
         connection.query(REALM_USER_BY_EMAIL, new DBQueryHandler() {
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setInt(1,realm.getId());
               s.setString(2, email);
            }
            public void onResults(ResultSet set)
               throws SQLException
            {
               if (set.next()) {
                  ruser.set(realmUserCaches.get(realm).get(set.getInt(1)));
               }
            }
         });
         if (ruser.get()!=null) {
            return ruser.get();
         }
         final User user = findUserByEmail(email);
         if (user!=null) {
            connection.query(REALM_USER_BY_USER, new DBQueryHandler() {
               public void prepare(PreparedStatement s)
                  throws SQLException
               {
                  s.setInt(1,realm.getId());
                  s.setInt(2, user.getId());
               }
               public void onResults(ResultSet set)
                  throws SQLException
               {
                  if (set.next()) {
                     ruser.set(realmUserCaches.get(realm).get(set.getInt(1)));
                  }
               }
            });
         }
         return ruser.get();
      } finally {
         release(connection);
      }
      
   }
   
   public RealmUser findRealmUser(final Realm realm,User user)
      throws SQLException
   {
      DBCache<UUID,RealmUser> cache = realmUserCaches.get(realm);
      RealmUser ruser = cache.get(user.getUUID());
      return ruser;
   }
   public Iterator<RealmUser> getRealmUsers(final Realm realm) 
      throws SQLException
   {
      final DBCache<UUID,RealmUser> cache = realmUserCaches.get(realm);
      final Slot<Iterator<RealmUser>> result = new Slot<Iterator<RealmUser>>();
      final DBConnection connection = getConnection();
      try {
         connection.query(REALM_USERS, new DBQueryHandler() {
            public boolean shouldClose() { return false; }
            public void prepare(PreparedStatement s)
               throws SQLException
            {
               s.setInt(1,realm.getId());
            }
            public void onResults(ResultSet set)
               throws SQLException
            {
               result.set(new DBIterator<RealmUser>(set,new DBResultConstructor<RealmUser>() {
                  public RealmUser newInstance(ResultSet set) 
                     throws SQLException
                  {
                     return cache.get(set.getInt(1));
                  }
               },AuthDB.this,connection));
            }
         });
      } finally {
         release(connection);
      }
      return result.get();
   }
   
   /**
    * @param realm The realm of the user (may be null)
    * @param session The session id of the authentication
    */
   public User.Authenticated isAuthenticated(final Realm realm,final UUID session)
      throws SQLException
   {
      final Slot<User.Authenticated> retval = new Slot<User.Authenticated>();
      DBConnection connection = getConnection();
      try {
         connection.query(USER_SESSION, new DBQueryHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setString(1,session.toString());
            }
            public void onResults(ResultSet set)
               throws SQLException
            {
               if (set.next()) {
                  Timestamp created = set.getTimestamp(2);
                  Timestamp expiration = set.getTimestamp(3);
                  User user = userCache.get(set.getInt(4));
                  User.Authenticated auth = new User.Authenticated(AuthDB.this,set.getInt(1),session,expiration,created,user,realm);
                  retval.set(auth);
               }
            }
         });
      } finally {
         release(connection);
      }
      User.Authenticated auth = retval.get();
      if (auth!=null && auth.isExpired()) {
         LOG.info("Session "+session.toString()+" has expired, deleting...");
         // the session has expired
         auth.delete();
         return null;
      } else {
         return auth;
      }
   }
   
   public void expireSession(final UUID session)
      throws SQLException
   {
      DBConnection connection = getConnection();
      try {
         connection.update(DELETE_SESSION, new DBUpdateHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setString(1,session.toString());
            }
         });
      } finally {
         release(connection);
      }
   }
   
   protected String getUserAlias(final int id)
      throws SQLException
   {
      final Slot<String> alias = new Slot<String>();
      DBConnection connection = getConnection();
      try {
         connection.query(USER_ALIAS, new DBQueryHandler() {
            public void prepare(PreparedStatement s) 
               throws SQLException
            {
               s.setInt(1,id);
            }
            public void onResults(ResultSet set)
               throws SQLException
            {
               if (set.next()) {
                  alias.set(set.getString(1));
               }
            }
         });
      } finally {
         release(connection);
      }
      return alias.get();
      
   }

   static int DELETE_USER_ROLES_BY_USER = 100;
   
   static int DELETE_AUTHENTICATION_BY_USER = 200;
   static int CREATE_AUTHENTICATION         = 201;
   
   static int DELETE_USER_ALIAS_BY_USER = 300;
   
   static int DELETE_REALM_USER                = 400;
   static int DELETE_REALM_USER_ALIAS          = 401;
   static int DELETE_REALM_USER_GROUPS         = 402;
   static int REALM_USERS_BY_USER              = 403;
   static int CREATE_REALM_USER_ALIAS          = 404;
   static int UPDATE_REALM_USER_ALIAS          = 405;
   static int REALM_USER                       = 406;
   static int REALM_USER_BY_ALIAS              = 408;
   static int REALM_USER_ALIAS                 = 409;
   static int REALM_USER_BY_USER               = 410;
   static int REALM_USER_BY_REALM              = 411;
   static int DELETE_AUTHENTICATED_BY_REALM    = 412;
   static int DELETE_REALM                     = 413;
   static int DELETE_AUTHENTICATED             = 414;
   static int CHANGE_REALM_USER_NAME           = 415;
   static int CHANGE_REALM_USER_EMAIL          = 416;
   static int REALM_USER_AUTHENTICATED         = 417;
   static int DELETE_REALM_USER_AUTHENTICATION = 418;
   static int REALM_USER_BY_INHERITED_ALIAS    = 419;
   static int CREATE_REALM_USER                = 420;
   static int LAST_ID_FROM_REALM_USERS         = 421;
   static int REALM_USER_BY_EMAIL              = 422;
   static int REALM_USERS                      = 423;
   static int REALM_USER_HAS_INHERITED_ALIAS   = 424;
   
   static int DELETE_USER                = 500;
   static int USER_HAS_ROLE              = 501;
   static int DELETE_USER_ROLE           = 502;
   static int ADD_USER_ROLE              = 503;
   static int USER_ROLES                 = 504;
   static int DELETE_USER_ALIAS          = 505;
   static int CHANGE_USER_ALIAS          = 506;
   static int CREATE_USER_ALIAS          = 507;
   static int CHANGE_USER_NAME           = 508;
   static int CHANGE_USER_EMAIL          = 509;
   static int USER_AUTHENTICATED         = 510;
   static int DELETE_USER_AUTHENTICATION = 511;
   static int DELETE_SESSION             = 512;
   static int DELETE_EXPIRED_SESSIONS    = 513;
   static int CREATE_AUTHENTICATED       = 514;
   static int CHECK_PASSWORD             = 515;
   static int ENCRYPTED_PASSWORD         = 516;
   static int USER_BY_ID                 = 517;
   static int USER_BY_UUID               = 518;
   static int USER_BY_ALIAS              = 519;
   static int USER_ALIAS                 = 520;
   static int USER_SESSION               = 521;
   static int USER_ALIAS_EXISTS          = 522;
   static int CREATE_USER                = 523;
   static int LAST_ID_FROM_USERS         = 524;
   static int USER_BY_EMAIL              = 525;
   static int USERS                      = 526;
   static int GLOBAL_USERS               = 527;
   
   static int REALM_BY_ID         = 600;
   static int REALM_BY_UUID       = 601;
   static int REALM_BY_NAME       = 602;
   static int CREATE_REALM        = 603;
   static int LAST_ID_FROM_REALMS = 604;
   static int REALMS              = 605;
   
   static int DELETE_PERMISSION                     = 700;
   static int DELETE_ROLE_PERMISSIONS_BY_PERMISSION = 701;
   static int PERMISSION_BY_ID                      = 702;
   static int PERMISSION_BY_NAME                    = 703;
   static int PERMISSION_BY_UUID                    = 704;
   static int CREATE_PERMISSION                     = 705;
   static int LAST_ID_FROM_PERMISSIONS              = 706;
   static int PERMISSIONS                           = 707;
   
   static int DELETE_ROLE                = 800;
   static int DELETE_ROLE_PERMISSIONS    = 801;
   static int DELETE_GROUP_ROLES_BY_ROLE = 802;
   static int DELETE_USER_ROLES_BY_ROLE  = 803;
   static int ROLE_PERMISSIONS           = 804;
   static int CREATE_ROLE_PERMISSION     = 805;
   static int DELETE_ROLE_PERMISSION     = 806;
   static int ROLE_BY_ID                 = 807;
   static int ROLE_BY_UUID               = 808;
   static int ROLE_BY_NAME               = 809;
   static int CREATE_ROLE                = 810;
   static int LAST_ID_FROM_ROLES         = 811;
   static int ROLES                      = 812;
   
   static int DELETE_GROUP         = 900;
   static int DELETE_GROUP_MEMBERS = 901;
   static int DELETE_GROUP_ROLES   = 902;
   static int GROUP_ROLES          = 903;
   static int GROUP_MEMBERS        = 904;
   static int CREATE_GROUP_ROLE    = 905;
   static int DELETE_GROUP_ROLE    = 906;
   static int CREATE_GROUP_MEMBER  = 907;
   static int DELETE_GROUP_MEMBER  = 908;
   static int GROUP_MEMBER         = 910;
   static int GROUP_BY_REALM       = 911;
   static int GROUP_BY_REALM_USER  = 912;
   static int GROUP_BY_ID          = 913;
   static int GROUP_BY_ALIAS       = 914;
   static int GROUP_BY_UUID        = 915;
   static int CREATE_GROUP         = 916;
   static int LAST_ID_FROM_GROUPS  = 917;
   static int GROUPS               = 918;
   
   protected void prepare(DBConnection dbConnection) 
      throws SQLException
   {
      dbConnection.addStatement(DELETE_USER_ROLES_BY_USER, "delete from auth.user_roles where user_ref=?");
      
      dbConnection.addStatement(DELETE_AUTHENTICATION_BY_USER,"delete from auth.authentication where user_ref=?");
      dbConnection.addStatement(CREATE_AUTHENTICATION,"insert into auth.authentication (user_ref,password_md5) values (?,?)");

      dbConnection.addStatement(DELETE_USER_ALIAS_BY_USER,"delete from auth.users_alias where user_ref=?");
      
      dbConnection.addStatement(DELETE_REALM_USER,"delete from auth.realm_users where id=?");
      dbConnection.addStatement(DELETE_REALM_USER_ALIAS,"delete from auth.realm_users_alias where realm_user_ref=?");
      dbConnection.addStatement(DELETE_REALM_USER_GROUPS,"delete from auth.group_members where realm_user_ref=?");
      dbConnection.addStatement(REALM_USERS_BY_USER,"select id,realm_ref from auth.realm_users where user_ref=?");
      dbConnection.addStatement(CREATE_REALM_USER_ALIAS,"insert into auth.realm_users_alias (realm_user_ref,alias,idalias) values (?,?,?)");
      dbConnection.addStatement(UPDATE_REALM_USER_ALIAS,"update auth.realm_users_alias set alias=?, idalias=? where realm_user_ref=?");
      dbConnection.addStatement(REALM_USER_BY_USER,"select id,name,email from auth.realm_users where realm_ref=? and user_ref=?");
      dbConnection.addStatement(REALM_USER_BY_ALIAS,"select u.id,user_ref,name,email from auth.realm_users u,auth.realm_users_alias where realm_ref=? and alias=? and realm_user_ref=u.id");
      dbConnection.addStatement(REALM_USER_ALIAS,"select alias from auth.realm_users_alias where realm_user_ref=?");
      dbConnection.addStatement(REALM_USER,"select user_ref,name,email from auth.realm_users where id=?");
      dbConnection.addStatement(REALM_USER_BY_REALM,"select id from auth.realm_users where realm_ref=?");
      dbConnection.addStatement(DELETE_AUTHENTICATED_BY_REALM,"delete from auth.authenticated where realm_ref=?");
      dbConnection.addStatement(DELETE_REALM,"delete from auth.realms where id=?");
      dbConnection.addStatement(DELETE_AUTHENTICATED,"delete from auth.authenticated where id=?");
      dbConnection.addStatement(CHANGE_REALM_USER_NAME,"update auth.realm_users set name=? where id=?");
      dbConnection.addStatement(CHANGE_REALM_USER_EMAIL,"update auth.realm_users set email=? where id=?");
      dbConnection.addStatement(REALM_USER_AUTHENTICATED,"select id,created,expiration from auth.authenticated where user_ref=? and realm_ref=? and uuid=?");
      dbConnection.addStatement(DELETE_REALM_USER_AUTHENTICATION,"delete from auth.authenticated where user_ref=? and realm_ref=? and uuid=?");
      dbConnection.addStatement(CREATE_AUTHENTICATED,"insert into auth.authenticated (user_ref,realm_ref,uuid,created,expiration) values(?,?,?,?,?)");
      dbConnection.addStatement(CHECK_PASSWORD,"select id from auth.authentication where user_ref=? and password_md5=?");
      dbConnection.addStatement(ENCRYPTED_PASSWORD,"select password_md5 from auth.authentication where user_ref=?");
      dbConnection.addStatement(REALM_USER_BY_INHERITED_ALIAS, "select id,name,email from auth.realm_users u where realm_ref=? and user_ref=? and not exists(select * from auth.realm_users_alias where realm_user_ref=u.id)");
      dbConnection.addStatement(CREATE_REALM_USER,"insert into auth.realm_users (realm_ref,user_ref,name,email,created,valid) values (?,?,?,?,?,?)");
      dbConnection.addStatement(LAST_ID_FROM_REALM_USERS,"select identity_val_local() from auth.realm_users");
      dbConnection.addStatement(REALM_USER_BY_EMAIL,"select id from auth.realm_users where realm_ref=? and email=?");
      dbConnection.addStatement(REALM_USERS,"select id from auth.realm_users where realm_ref=?");
      dbConnection.addStatement(REALM_USER_HAS_INHERITED_ALIAS, "select ru.id from auth.realm_users ru, auth.users u, auth.users_alias a where realm_ref=? and not exists(select * from auth.realm_users_alias where realm_user_ref=u.id) and ru.user_ref=u.id and a.user_ref=u.id and a.alias=?");
      
      dbConnection.addStatement(DELETE_USER,"delete from auth.users where id=?");
      dbConnection.addStatement(USER_HAS_ROLE,"select id from auth.user_roles where user_ref=? and role_ref=?");
      dbConnection.addStatement(DELETE_USER_ROLE,"delete from auth.user_roles where user_ref=? and role_ref=?");
      dbConnection.addStatement(DELETE_USER_ROLE,"delete from auth.user_roles where user_ref=? and role_ref=?");
      dbConnection.addStatement(ADD_USER_ROLE,"insert into auth.user_roles (user_ref,role_ref) values (?,?)");
      dbConnection.addStatement(USER_ROLES,"select role_ref from auth.user_roles where user_ref=?");
      dbConnection.addStatement(DELETE_USER_ALIAS,"delete from auth.users_alias where user_ref=?");
      dbConnection.addStatement(CHANGE_USER_ALIAS,"update auth.users_alias set alias=?,idalias=? where user_ref=?");
      dbConnection.addStatement(CREATE_USER_ALIAS,"insert into auth.users_alias (user_ref,alias,idalias) values (?,?,?)");
      dbConnection.addStatement(CHANGE_USER_NAME,"update auth.users set name=? where id=?");
      dbConnection.addStatement(CHANGE_USER_EMAIL,"update auth.users set email=? where id=?");
      dbConnection.addStatement(USER_AUTHENTICATED,"select id,created,expiration from auth.authenticated where user_ref=? and realm_ref is null and uuid=?");
      dbConnection.addStatement(DELETE_USER_AUTHENTICATION,"delete from auth.authenticated where user_ref=? and realm_ref is null and uuid=?");
      dbConnection.addStatement(DELETE_SESSION,"delete from auth.authenticated where uuid=?");
      dbConnection.addStatement(DELETE_EXPIRED_SESSIONS,"delete from auth.authenticated where user_ref=? and expiration<?");
      dbConnection.addStatement(USER_BY_ID,"select id,name,email,uuid from auth.users where id=?");
      dbConnection.addStatement(USER_BY_UUID,"select id,name,email,uuid from auth.users where uuid=?");
      dbConnection.addStatement(USER_BY_ALIAS,"select u.id,name,email,uuid from auth.users u,auth.users_alias where alias=? and u.id=user_ref");
      dbConnection.addStatement(USER_ALIAS,"select alias from auth.users_alias where user_ref=?");
      dbConnection.addStatement(USER_SESSION,"select id,created,expiration,user_ref from auth.authenticated where uuid=?");
      dbConnection.addStatement(USER_ALIAS_EXISTS, "select id from auth.users_alias where alias=?");
      dbConnection.addStatement(CREATE_USER,"insert into auth.users (uuid,name,email,created,valid) values (?,?,?,?,?)");
      dbConnection.addStatement(LAST_ID_FROM_USERS,"select identity_val_local() from auth.users");
      dbConnection.addStatement(USER_BY_EMAIL,"select id from auth.users where email=?");
      dbConnection.addStatement(USERS,"select id from auth.users");
      dbConnection.addStatement(GLOBAL_USERS,"select u.id from auth.users u, auth.users_alias a where u.id=a.user_ref and a.alias is not null");
      
      dbConnection.addStatement(REALM_BY_ID,"select id,name,uuid from auth.realms where id=?");
      dbConnection.addStatement(REALM_BY_UUID,"select id,name,uuid from auth.realms where uuid=?");
      dbConnection.addStatement(REALM_BY_NAME,"select id,name,uuid from auth.realms where name=?");
      dbConnection.addStatement(CREATE_REALM,"insert into auth.realms (name,uuid) values (?,?)");
      dbConnection.addStatement(LAST_ID_FROM_REALMS,"select identity_val_local() from auth.realms");
      dbConnection.addStatement(REALMS,"select id from auth.realms");
      
      dbConnection.addStatement(DELETE_PERMISSION,"delete from auth.permissions where id=?");
      dbConnection.addStatement(DELETE_ROLE_PERMISSIONS_BY_PERMISSION,"delete from auth.role_permissions where permission_ref=?");
      dbConnection.addStatement(PERMISSION_BY_ID,"select id,name,uuid from auth.permissions where id=?");
      dbConnection.addStatement(PERMISSION_BY_NAME,"select id,name,uuid from auth.permissions where name=?");
      dbConnection.addStatement(PERMISSION_BY_UUID,"select id,name,uuid from auth.permissions where uuid=?");
      dbConnection.addStatement(CREATE_PERMISSION,"insert into auth.permissions (name,uuid) values (?,?)");
      dbConnection.addStatement(LAST_ID_FROM_PERMISSIONS,"select identity_val_local() from auth.permissions");
      dbConnection.addStatement(PERMISSIONS,"select id from auth.permissions");
      
      dbConnection.addStatement(DELETE_ROLE,"delete from auth.roles where id=?");
      dbConnection.addStatement(DELETE_ROLE_PERMISSIONS,"delete from auth.role_permissions where role_ref=?");
      dbConnection.addStatement(DELETE_GROUP_ROLES_BY_ROLE,"delete from auth.group_roles where role_ref=?");
      dbConnection.addStatement(DELETE_USER_ROLES_BY_ROLE,"delete from auth.user_roles where role_ref=?");
      dbConnection.addStatement(ROLE_PERMISSIONS,"select permission_ref from auth.role_permissions where role_ref=?");
      dbConnection.addStatement(CREATE_ROLE_PERMISSION,"insert into auth.role_permissions (role_ref,permission_ref) values (?,?)");
      dbConnection.addStatement(DELETE_ROLE_PERMISSION,"delete from auth.role_permissions where role_ref=? and permission_ref=?");
      dbConnection.addStatement(ROLE_BY_ID,"select id,name,uuid from auth.roles where id=?");
      dbConnection.addStatement(ROLE_BY_UUID,"select id,name,uuid from auth.roles where uuid=?");
      dbConnection.addStatement(ROLE_BY_NAME,"select id,name,uuid from auth.roles where name=?");
      dbConnection.addStatement(CREATE_ROLE,"insert into auth.roles (name,uuid) values (?,?)");
      dbConnection.addStatement(LAST_ID_FROM_ROLES,"select identity_val_local() from auth.roles");
      dbConnection.addStatement(ROLES,"select id from auth.roles");
      
      dbConnection.addStatement(DELETE_GROUP,"delete from auth.groups where id=?");
      dbConnection.addStatement(DELETE_GROUP_MEMBERS,"delete from auth.group_members where group_ref=?");
      dbConnection.addStatement(DELETE_GROUP_ROLES,"delete from auth.group_roles where group_ref=?");
      dbConnection.addStatement(GROUP_ROLES,"select role_ref from auth.group_roles where group_ref=?");
      dbConnection.addStatement(GROUP_MEMBERS,"select realm_user_ref from auth.group_members where group_ref=?");
      dbConnection.addStatement(CREATE_GROUP_ROLE,"insert into auth.group_roles (group_ref,role_ref) values (?,?)");
      dbConnection.addStatement(DELETE_GROUP_ROLE,"delete from auth.group_roles where group_ref=? and role_ref=?");
      dbConnection.addStatement(CREATE_GROUP_MEMBER,"insert into auth.group_members (group_ref,realm_user_ref) values (?,?)");
      dbConnection.addStatement(DELETE_GROUP_MEMBER,"delete from auth.group_members where group_ref=? and realm_user_ref=?");
      dbConnection.addStatement(GROUP_MEMBER,"select id from auth.group_members where group_ref=? and realm_user_ref=?");
      dbConnection.addStatement(GROUP_BY_REALM,"select id from auth.groups where realm_ref=?");
      dbConnection.addStatement(GROUP_BY_REALM_USER,"select g.id,g.alias from auth.groups g,auth.group_members where realm_ref=? and g.id=group_ref and realm_user_ref=?");
      dbConnection.addStatement(GROUP_BY_ID,"select id,alias,uuid from auth.groups where realm_ref=? and id=?");
      dbConnection.addStatement(GROUP_BY_UUID,"select id,alias,uuid from auth.groups where realm_ref=? and uuid=?");
      dbConnection.addStatement(GROUP_BY_ALIAS,"select id,alias,uuid from auth.groups where realm_ref=? and alias=?");
      dbConnection.addStatement(CREATE_GROUP,"insert into auth.groups (realm_ref,uuid,alias,idalias) values (?,?,?,?)");
      dbConnection.addStatement(LAST_ID_FROM_GROUPS,"select identity_val_local() from auth.groups");
      dbConnection.addStatement(GROUPS,"select id from auth.groups where realm_ref=?");
      
   }
   
   protected void loadSchema(String name,Connection connection,URL sqlLocation)
      throws java.io.IOException,java.sql.SQLException
   {
      super.loadSchema(name, connection, sqlLocation);
      if (name.equals("auth")) {
         URL defaultDB = this.getClass().getResource("default-db.xml");
         if (defaultDB!=null) {
            try {
               DocumentLoader loader = new SAXDocumentLoader();
               RestoreDestination restore = new RestoreDestination(LOG,this);
               loader.generate(defaultDB.toURI(), restore);
            } catch (XMLException ex) {
               throw new IOException(ex.getMessage());
            } catch (URISyntaxException ex) {
               throw new IOException(ex.getMessage());
            }
         }
      }
   }
   
}
